﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace LOGISTCBUS.DAO
{
    public class Db : IDisposable
    {
        #region properties
        private SqlConnection _connection;
        private SqlTransaction _transaction;
        public static String StringConnection { get; set; }
        #endregion

        #region public methods
        public void Connect()
        {
            this._connection = new SqlConnection(StringConnection);
            if (!(this._connection.State == ConnectionState.Open))
            {
                this._connection.Open();
                this._transaction = this._connection.BeginTransaction();
            }
        }

        public void Disconnect()
        {
            this._connection.Close();
            this._transaction.Dispose();
        }

        public void Commit()
        {
            this._transaction.Commit();
        }

        public void Rollback()
        {
            if (this._transaction != null)
                this._transaction.Rollback();
        }

        public void Dispose()
        {
            this.Disconnect();
        }

        public bool Execute(string sql)
        {
            IDbCommand cmd = new SqlCommand();
            cmd.Connection = _connection;
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;
            Debug.WriteLine(sql);
            return cmd.ExecuteNonQuery() > 0;
        }

        public bool Execute(params string[] sqls)
        {
            IDbCommand cmd = new SqlCommand();
            cmd.Connection = _connection;
            foreach (var s in sqls)
            {
                cmd.CommandText = s;
                cmd.ExecuteNonQuery();
                Debug.WriteLine(s);
            }
            return true;
        }

        public bool Execute(string sql, Dictionary<String, Object> colParams)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _connection;
            cmd.CommandText = sql;
            cmd.Transaction = _transaction;
            foreach (var kv in colParams)
                cmd.Parameters.Add(new SqlParameter(kv.Key, kv.Value ?? DBNull.Value));
            Debug.WriteLine(sql);
            return cmd.ExecuteNonQuery() > 0;
        }

        public object ExecuteScalar(string sql)
        {
            IDbCommand cmd = new SqlCommand();
            cmd.Connection = _connection;
            cmd.CommandText = sql;
            cmd.Transaction = _transaction;
            Debug.WriteLine(sql);
            return cmd.ExecuteScalar();
        }

        public SqlConnection GetConnection()
        {
            return this._connection;
        }

        public SqlTransaction GetTransaction()
        {
            return this._transaction;
        }

        public DataTable Select(string sql)
        {
            var dt = new DataTable();
            dt.Load(GetReader(sql, GetConnection()));
            Debug.WriteLine(sql);
            return dt;
        }

        public DataTable Select(string sql, Dictionary<String, Object> colParams)
        {
            var dt = new DataTable();
            dt.Load(GetReader(sql, GetConnection(), colParams));
            Debug.WriteLine(sql);
            return dt;
        }

        public void SetConnection(SqlConnection connection)
        {
            _connection = connection;
        }
        #endregion

        #region private methods
        IDataReader GetReader(string sql, SqlConnection connection)
        {
            var cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.CommandText = sql;
            cmd.Transaction = _transaction;
            return cmd.ExecuteReader();
        }

        IDataReader GetReader(string sql, SqlConnection connection, Dictionary<String, Object> colParams)
        {
            var cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.CommandText = sql;
            cmd.Transaction = _transaction;
            foreach (var kv in colParams)
                cmd.Parameters.Add(new SqlParameter(kv.Key, kv.Value));
            return cmd.ExecuteReader();
        }
        #endregion
    }
}
